<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>读取 Excel 文件</title>
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
            /* 设置表格布局为 fixed */
            table-layout: fixed;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
        /* 设置表格第一列宽度为 180px */
        td:first-child, th:first-child {
            width: 180px !important;
        }
        h1 {
            text-align: left;
        }
    </style>
</head>
<body>
    <input type="file" id="excelFileInput" accept=".xlsx, .xls">
    <!-- 添加下拉选择器 -->
    <select id="sheetSelector" style="margin: 10px 0;">
        <option value="">选择工作表</option>
    </select>
    <!-- 添加新表格容器 -->
    <div id="expandedTableContainer"></div>
    <div id="tablesContainer"></div>

    <!-- 引入 SheetJS 库 -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    <script>
        const fileInput = document.getElementById('excelFileInput');
        const tablesContainer = document.getElementById('tablesContainer');
        const sheetSelector = document.getElementById('sheetSelector');
        const expandedTableContainer = document.getElementById('expandedTableContainer');
        let allTables = {};

        fileInput.addEventListener('change', function(e) {
            const file = e.target.files[0];
            if (file) {
                const reader = new FileReader();
                reader.onload = function(e) {
                    const data = new Uint8Array(e.target.result);
                    const workbook = XLSX.read(data, {type: 'array'});

                    // 要读取的 sheet 名称
                    const sheetNames = ['国产', '进口'];
                    // 清空下拉选择器和表格容器
                    sheetSelector.innerHTML = '<option value="">选择工作表</option>';
                    tablesContainer.innerHTML = '';
                    expandedTableContainer.innerHTML = '';
                    allTables = {};

                    sheetNames.forEach(sheetName => {
                        const worksheet = workbook.Sheets[sheetName];
                        if (worksheet) {
                            const jsonData = XLSX.utils.sheet_to_json(worksheet, {header: 1});
                            const table = createTable(jsonData, sheetName);
                            allTables[sheetName] = table;
                            // 添加下拉选项
                            const option = document.createElement('option');
                            option.value = sheetName;
                            option.textContent = sheetName;
                            sheetSelector.appendChild(option);
                        }
                    });
                    // 默认显示第一个表格
                    if (sheetNames.length > 0) {
                        tablesContainer.appendChild(allTables[sheetNames[0]]);
                        sheetSelector.value = sheetNames[0];
                    }
                };
                reader.readAsArrayBuffer(file);
            }
        });

        // 为下拉选择器添加事件监听器
        sheetSelector.addEventListener('change', function() {
            const selectedSheet = this.value;
            tablesContainer.innerHTML = '';
            expandedTableContainer.innerHTML = '';
            if (selectedSheet && allTables[selectedSheet]) {
                tablesContainer.appendChild(allTables[selectedSheet]);
            }
        });

        function createTable(data, sheetName) {
            const table = document.createElement('table');
            const caption = document.createElement('caption');
            caption.textContent = sheetName;
            table.appendChild(caption);

            // 创建展开表格
            const expandedTable = document.createElement('table');
            const expandedCaption = document.createElement('caption');
            expandedCaption.textContent = '行展开和列展开';
            expandedTable.appendChild(expandedCaption);

            // 创建表头
            const headerRow = document.createElement('tr');
            const rowHeader = document.createElement('th');
            rowHeader.textContent = '行展开';
            headerRow.appendChild(rowHeader);
            const colHeader = document.createElement('th');
            colHeader.textContent = '列展开';
            headerRow.appendChild(colHeader);
            expandedTable.appendChild(headerRow);

            // 创建两行数据行
            const rowDataRow = document.createElement('tr');
            const colDataRow = document.createElement('tr');
            // 为每行添加占位单元格
            for (let i = 0; i < data[0].length; i++) {
                const rowCell = document.createElement('td');
                rowDataRow.appendChild(rowCell);
                const colCell = document.createElement('td');
                colDataRow.appendChild(colCell);
            }
            expandedTable.appendChild(rowDataRow);
            expandedTable.appendChild(colDataRow);

            // 将展开表格添加到容器
            expandedTableContainer.appendChild(expandedTable);

            data.forEach((rowData, rowIndex) => {
                const row = document.createElement('tr');
                rowData.forEach((cellData, colIndex) => {
                    const cell = document.createElement('td');
                    // 尝试将 cellData 转换为数字
                    const num = parseFloat(cellData);
                    let value;
                    if (!isNaN(num)) {
                        if (num === 0) {
                            value = ''; // 若数字为 0，设置为空字符串
                        } else {
                            value = (num / 10000).toFixed(2); // 除以 10000 并保留两位小数
                        }
                    } else {
                        value = cellData;
                    }
                    cell.textContent = value !== undefined && value !== null ? value : '';
                    // 设置列宽度
                    if (colIndex === 0) {
                        cell.style.width = '180px';
                    } else {
                        cell.style.width = '80px';
                    }
                    // 为单元格添加点击事件监听器
                    cell.addEventListener('click', function() {
                        // 移除之前选中的行和列的背景色
                        const allCells = table.querySelectorAll('td, th');
                        allCells.forEach(c => {
                            c.style.backgroundColor = '';
                        });

                        // 设置当前行的背景色
                        const rowCells = row.querySelectorAll('td, th');
                        rowCells.forEach(c => {
                            c.style.backgroundColor = 'lightgreen';
                        });

                        // 设置当前列的背景色
                        const colCells = table.querySelectorAll(`tr td:nth-child(${colIndex + 1}), tr th:nth-child(${colIndex + 1})`);
                        colCells.forEach(c => {
                            c.style.backgroundColor = 'lightgreen';
                        });

                        // 更新展开表格的行数据
                        rowData.forEach((cellValue, index) => {
                            const rowCell = rowDataRow.cells[index];
                            rowCell.textContent = cellValue;
                        });

                        // 更新展开表格的列数据
                        const allRows = table.rows;
                        for (let i = 0; i < allRows.length; i++) {
                            const colCell = allRows[i].cells[colIndex];
                            const expandedColCell = colDataRow.cells[i];
                            expandedColCell.textContent = colCell.textContent;
                        }
                    });
                    row.appendChild(cell);
                });
                table.appendChild(row);
            });
            return table;
        }
    </script>
</body>
</html>